#!/usr/bin/env python
# encoding: utf-8
'''
@author: liuchong
@contact: lucas@007vin.com
@software: garner
@file: banzhou.py
@time: 2020/8/24 2:44 下午
@desc:
'''

from pymongo import MongoClient
import pandas as pd

from pymongo import MongoClient
host = '192.168.191.76'
client = MongoClient(host, 27017)
#连接mydb数据库,账号密码认证
db = client.admin    # 连接系统默认数据库admin
db.authenticate('root', 'aa123456')
db_cars = client.land_rover
db_price = client.price
collection_cars = db_cars.market_car_part_collection
collection_price = db_price.car_market_name_table
shuiB = collection_cars.find({'standard_label' :{'$regex':'轮毂'}},{'_id':0,'pid':1, 'market_name':1, 'car_id':1})
shuiBitems = []

for shuiBinfo in shuiB:
    engineCodeinfo = collection_price.find_one({'car_id':shuiBinfo.get('car_id')},{'_id':0,'engine_code':1})
    shuiBinfo['发动机号'] = engineCodeinfo.get('engine_code') if engineCodeinfo else ''
    shuiBitems.append(shuiBinfo)
df_sb = pd.DataFrame(shuiBitems)
df_sb['pid'] = df_sb['pid'].astype('str')
df_sb.drop_duplicates(subset='pid', keep='first', inplace=True)
df_sb.rename(columns={'pid':'OE','market_name':'车型'},inplace=True)
df_sb.drop('car_id',axis=1, inplace=True)
order = ['OE','车型','发动机号']
df_sb = df_sb[order]
df_sb.to_excel('./export/路虎_轮毂.xlsx', index=False)

